﻿
'=====================================================================
'模 块 名：EPPlus

'描    述：Excel读写操作类

'依赖模块：EPPlus

'使用方法：(1)OpenExcel()打开Excel文件
'          (2)ReadExcel()读文件  WriteExcel()写文件
'          (3)CloseExcel()关闭文件

'原始版本：1.00
'作    者：费鸣杰
'完成日期：2021-08-12

'修改版本：1.01
'修改作者：费鸣杰
'修改日期：2021-11-19
'修改记录：1) WriteExcel() 增加浮点型参数

'修改版本：1.02
'修改作者：刘德亮
'修改日期：2021-11-22
'修改记录：1) WriteExcel() 增加整型参数
'          
'=====================================================================
Imports System.IO
Imports OfficeOpenXml
Imports OfficeOpenXml.VBA

Namespace FL_8x0.Common

    Public Class EPPlus

        '错误号
        Public Const EPPLUS_ERR_OK As UInt16 = &H0          'OK
        Public Const EPPLUS_ERR_PARAM As UInt16 = &H1       '参数错误
        Public Const EPPLUS_ERR_OPEN As UInt16 = &H2        '打开文件错误
        Public Const EPPLUS_ERR_READ As UInt16 = &H3        '读取文件错误
        Public Const EPPLUS_ERR_WRITE As UInt16 = &H4       '写入文件错误
        Public Const EPPLUS_ERR_SAVE As UInt16 = &H5        '保存文件错误
        Public Const EPPLUS_ERR_OTHER As UInt16 = &HFFFF    '其它错误

        Public MyExcelPackage As ExcelPackage = Nothing     'EPPlus入口类
        'Private MyExcelWorkbook As ExcelWorkbook = Nothing   'EPPlus Workbook
        Public MyExcelWorksheet As ExcelWorksheet = Nothing 'EPPlus Worksheet



        '=====================================================================
        '函 数 名: New
        '参    数: 
        '返 回 值:
        '功能描述: 构造函数
        '=====================================================================
        Public Sub New()


        End Sub


        '=====================================================================
        '函 数 名: Finalize
        '参    数: 
        '返 回 值:
        '功能描述: 析构函数
        '=====================================================================
        'Protected Overrides Sub Finalize()
        '    Try
        '        If Not IsNothing(MyExcelWorksheet) Then
        '            MyExcelWorksheet.Dispose()
        '            MyExcelWorksheet = Nothing
        '        End If

        '        If Not IsNothing(MyExcelPackage) Then
        '            MyExcelPackage.Dispose()
        '            MyExcelPackage = Nothing
        '        End If
        '    Catch ex As Exception
        '        MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
        '    End Try

        '    MyBase.Finalize()
        'End Sub


        '=====================================================================
        '函 数 名:OpenExcel
        '参    数:fFileInfo   = 打开文件信息
        '
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:打开Excel文件
        '=====================================================================
        Public Function OpenExcel(ByVal fFileInfo As FileInfo) As UInt16
            If Not fFileInfo.Exists Then
                Return EPPLUS_ERR_OPEN
            End If

            Try
                MyExcelPackage = New ExcelPackage(fFileInfo)
                If IsNothing(MyExcelPackage) Then
                    Return EPPLUS_ERR_OPEN
                End If

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


        '=====================================================================
        '函 数 名:OpenExcel
        '参    数:fFileStream   = 打开文件流
        '
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:打开Excel文件
        '=====================================================================
        Public Function OpenExcel(ByVal fFileStream As FileStream) As UInt16
            Try
                MyExcelPackage = New ExcelPackage(fFileStream)
                If IsNothing(MyExcelPackage) Then
                    Return EPPLUS_ERR_OPEN
                End If

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


        '=====================================================================
        '函 数 名:OpenExcel
        '参    数:strFilePath = 打开文件路径
        '
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:打开Excel文件
        '=====================================================================
        Public Function OpenExcel(ByVal strFilePath As String) As UInt16
            Dim fFile As FileInfo = New FileInfo(strFilePath)

            Return OpenExcel(fFile)
        End Function


        '=====================================================================
        '函 数 名:QuitExcel
        '参    数:
        '
        '返 回 值:
        '        
        '功能描述:退出Excel程序
        '=====================================================================
        Public Shared Sub QuitExcel()
            Try
                Dim p As System.Diagnostics.Process
                p = New System.Diagnostics.Process
                For Each p In System.Diagnostics.Process.GetProcesses()
                    If p.ProcessName.ToUpper() = "EXCEL" Then
                        p.Kill()
                        p.WaitForExit()
                    End If
                Next

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, "错误")
            End Try
        End Sub


        '=====================================================================
        '函 数 名:CloseExcel
        '参    数:bIsCloseApp = 是否退出Excel程序
        '
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:关闭Excel文件
        '=====================================================================
        Public Sub CloseExcel(ByVal bIsCloseApp As Boolean)
            Try
                If Not IsNothing(MyExcelWorksheet) Then
                    MyExcelWorksheet.Dispose()
                    MyExcelWorksheet = Nothing
                End If

                If Not IsNothing(MyExcelPackage) Then
                    MyExcelPackage.Dispose()
                    MyExcelPackage = Nothing
                End If

                If bIsCloseApp = True Then
                    QuitExcel()
                End If
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, "错误")
            End Try
        End Sub


        '=====================================================================
        '函 数 名:ReadExcel
        '参    数:strWorksheet  = 工作表名称字符串     
        '         strCell       = 写入的单元格位置字符串，如"A1"
        '         
        '返 回 值:读取到的数据字符串
        '         
        '功能描述:写入Excel文件
        '=====================================================================
        Public Function ReadExcel(ByVal strWorksheet As String, ByVal strCell As String) As String
            If IsNothing(MyExcelPackage) Then
                Return Nothing
            End If

            Try
                MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets(strWorksheet)
                If IsNothing(MyExcelWorksheet) Then
                    Return Nothing
                End If

                Return MyExcelWorksheet.Cells(strCell).Value

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return Nothing
        End Function


        '=====================================================================
        '函 数 名:WriteExcel
        '参    数:strWorksheet  = 工作表名称字符串     
        '         strCell       = 写入的单元格位置字符串，如"A1"
        '         strData       = 要写入的数据字符串
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:写入Excel文件
        '=====================================================================
        Public Function WriteExcel(ByVal strWorksheet As String, ByVal strCell As String, ByVal strData As String) As UInt16
            If IsNothing(MyExcelPackage) Then
                Return EPPLUS_ERR_OPEN
            End If

            Try
                MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets(strWorksheet)
                If IsNothing(MyExcelWorksheet) Then
                    MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets.Add(strWorksheet)
                    If IsNothing(MyExcelWorksheet) Then
                        Return EPPLUS_ERR_OPEN
                    End If
                End If

                MyExcelWorksheet.Cells(strCell).Value = strData

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


        '=====================================================================
        '函 数 名:WriteExcel
        '参    数:strWorksheet  = 工作表名称字符串     
        '         strCell       = 写入的单元格位置字符串，如"A1"
        '         sngData       = 要写入的浮点型数据
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:写入Excel文件
        '=====================================================================
        Public Function WriteExcel(ByVal strWorksheet As String, ByVal strCell As String, ByVal sngData As Single) As UInt16
            If IsNothing(MyExcelPackage) Then
                Return EPPLUS_ERR_OPEN
            End If

            Try
                MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets(strWorksheet)
                If IsNothing(MyExcelWorksheet) Then
                    MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets.Add(strWorksheet)
                    If IsNothing(MyExcelWorksheet) Then
                        Return EPPLUS_ERR_OPEN
                    End If
                End If

                MyExcelWorksheet.Cells(strCell).Value = sngData

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


        '=====================================================================
        '函 数 名:WriteExcel
        '参    数:strWorksheet  = 工作表名称字符串     
        '         strCell       = 写入的单元格位置字符串，如"A1"
        '         lngData       = 要写入的整型数据
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:写入Excel文件
        '=====================================================================
        Public Function WriteExcel(ByVal strWorksheet As String, ByVal strCell As String, ByVal lngData As Long) As UInt16
            If IsNothing(MyExcelPackage) Then
                Return EPPLUS_ERR_OPEN
            End If

            Try
                MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets(strWorksheet)
                If IsNothing(MyExcelWorksheet) Then
                    MyExcelWorksheet = MyExcelPackage.Workbook.Worksheets.Add(strWorksheet)
                    If IsNothing(MyExcelWorksheet) Then
                        Return EPPLUS_ERR_OPEN
                    End If
                End If

                MyExcelWorksheet.Cells(strCell).Value = lngData

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


        '=====================================================================
        '函 数 名:SaveExcel
        '参    数:strNewFilePath = 保存新的文件路径(Nothing=保存到原文件路径)
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:保存Excel文件
        '=====================================================================
        Public Function SaveExcel(ByVal strNewFilePath As String) As UInt16
            If IsNothing(MyExcelPackage) Then
                Return EPPLUS_ERR_OPEN
            End If

            Try
                If Not IsNothing(strNewFilePath) Then
                    Dim fNewFile As New FileInfo(strNewFilePath)
                    MyExcelPackage.SaveAs(fNewFile)
                Else
                    MyExcelPackage.Save()
                End If

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


        '=====================================================================
        '函 数 名:WriteVBA
        '参    数:strVBAName  = VBA模块名称
        '         strVBACode  = VBA字符串  
        '返 回 值:错误号
        '         EPPLUS_ERR_xxx
        '功能描述:写入VBA代码
        '=====================================================================
        Public Function WriteVBA(ByVal strVBAName As String, ByVal strVBACode As String) As UInt16
            If IsNothing(MyExcelPackage) Then
                Return EPPLUS_ERR_OPEN
            End If

            If IsNothing(MyExcelWorksheet) Then
                Return EPPLUS_ERR_OPEN
            End If

            Try
                MyExcelPackage.Workbook.CreateVBAProject()
                Dim VbaProj As ExcelVbaProject = MyExcelPackage.Workbook.VbaProject
                If Not IsNothing(strVBAName) Then
                    MyExcelPackage.Workbook.CodeModule.Name = strVBAName
                Else
                    MyExcelPackage.Workbook.CodeModule.Name = "ThisWorkbook"
                End If
                MyExcelPackage.Workbook.CodeModule.Code = strVBACode

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.Source)
            End Try

            Return EPPLUS_ERR_OK
        End Function


    End Class
End Namespace